﻿using BCCommon;
using BCEntity.IM.LeaveMessageRecord;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BCData.IM.LeaveMessageRecord
{
    /// <summary>
    /// 留言记录数据处理
    /// </summary>
    public class LeaveMessageRecordData : ILeaveMessageRecordData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT LeaveMessageRecordId,Question,UserName,Phone,IsRead,CreateTime,
                                              IsReply,ReplyTime,ReplyContent,ReplyUserName,ReplyAdminId,MarkUserName,MarkAdminId  
                                        FROM LeaveMessageRecord";
        public LeaveMessageRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public LeaveMessageRecordEntity Add(LeaveMessageRecordEntity leaveMessageRecordEntity)
        {
            string sql = string.Format(@"INSERT INTO LeaveMessageRecord(Question,UserName,Phone)VALUES(?Question,?UserName,?Phone);
                           {0} WHERE LeaveMessageRecordId=LAST_INSERT_ID();", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Question", leaveMessageRecordEntity.Question, DbType.AnsiString, size: 600);
            parameters.Add("?UserName", leaveMessageRecordEntity.UserName, DbType.AnsiString, size: 30);
            parameters.Add("?Phone", leaveMessageRecordEntity.Phone, DbType.AnsiString, size: 20);
            return this.databaseContext.Master.QueryEntity<LeaveMessageRecordEntity>(sql, parameters,databaseContext.Transaction);
        }

        public void Delete(DateTime? start, DateTime? end)
        {
            string sql = @"DELETE FROM LeaveMessageRecord WHERE CreateTime>=?Start AND CreateTime<=?End;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Start", start, DbType.DateTime);
            parameters.Add("?End", end, DbType.DateTime);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public IEnumerable<LeaveMessageRecordEntity> Query(bool? isRead, bool? isReply, string replyAdminName, DateTime? start, DateTime? end, bool? ascending, out int dataCount, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(replyAdminName))
            {
                wheres.Add("ReplyUserName=?ReplyUserName");
                parameters.Add("?ReplyUserName", replyAdminName, DbType.AnsiString, size: 30);
            }
            if (isRead.HasValue)
            {
                wheres.Add("IsRead=?IsRead");
                parameters.Add("?IsRead", isRead.Value, DbType.Boolean);
            }
            if (isReply.HasValue)
            {
                wheres.Add("IsReply=?IsReply");
                parameters.Add("?IsReply", isReply.Value, DbType.Boolean);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            string newSql = string.Format(@"{0} {1} ORDER BY IsRead ASC,CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM LeaveMessageRecord {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var result = this.databaseContext.Slave.QueryMultiple<LeaveMessageRecordEntity, int>(newSql, parameters);

            dataCount = result.Item2;

            return result.Item1;
        }

        public LeaveMessageRecordEntity UpdateIsRead(long leaveMessageRecordId, long markAdminId, string markUserName)
        {
            string sql = @"UPDATE LeaveMessageRecord SET
                                    IsRead=1,MarkAdminId=?MarkAdminId,MarkUserName=?MarkUserName 
                           WHERE LeaveMessageRecordId=?LeaveMessageRecordId;
                            {0} WHERE LeaveMessageRecordId=?LeaveMessageRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LeaveMessageRecordId", leaveMessageRecordId, DbType.Int64);
            parameters.Add("?MarkAdminId", markAdminId, DbType.Int64);
            parameters.Add("?MarkUserName", markUserName, DbType.AnsiString);
            return this.databaseContext.Master.QueryEntity<LeaveMessageRecordEntity>(string.Format(sql, QUERY), parameters,databaseContext.Transaction);
        }

        public LeaveMessageRecordEntity UpdateReplyInfo(LeaveMessageRecordEntity leaveMessageRecordEntity)
        {
            string sql = @"UPDATE LeaveMessageRecord SET
                                    IsReply=1,
                                    ReplyTime=?ReplyTime,
                                    ReplyContent=?ReplyContent,
                                    ReplyUserName=?ReplyUserName,
                                    ReplyAdminId=?ReplyAdminId
                           WHERE LeaveMessageRecordId=?LeaveMessageRecordId;
                            {0} WHERE LeaveMessageRecordId=?LeaveMessageRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LeaveMessageRecordId", leaveMessageRecordEntity.LeaveMessageRecordId, DbType.Int64);
            parameters.Add("?ReplyTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?ReplyContent", leaveMessageRecordEntity.ReplyContent, DbType.String);
            parameters.Add("?ReplyUserName", leaveMessageRecordEntity.ReplyUserName, DbType.AnsiString, size: 30);
            parameters.Add("?ReplyAdminId", leaveMessageRecordEntity.ReplyAdminId, DbType.Int64);
            return this.databaseContext.Master.QueryEntity<LeaveMessageRecordEntity>(string.Format(sql, QUERY), parameters,databaseContext.Transaction);
        }

        public bool CheckLeaveMessageAlreadyReplied(long leaveMessageRecordId)
        {
            string sql = @"SELECT 1 FROM LeaveMessageRecord WHERE LeaveMessageRecordId=?LeaveMessageRecordId AND IsReply=1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LeaveMessageRecordId", leaveMessageRecordId, DbType.Int64);
            object obj = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return obj != null;
        }

        public LeaveMessageRecordEntity Get(long leaveMessageRecordId)
        {
            string sql = @"{0} WHERE LeaveMessageRecordId=?LeaveMessageRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?LeaveMessageRecordId", leaveMessageRecordId, DbType.Int64);
            return this.databaseContext.Slave.QueryEntity<LeaveMessageRecordEntity>(string.Format(sql, QUERY), parameters);
        }

        public LeaveMessageCountEntity QueryCount()
        {
            string sql =
                @"SELECT COUNT(A.LeaveMessageRecordId) AS TodayCount,COUNT(B.LeaveMessageRecordId) AS NotRead,COUNT(C.LeaveMessageRecordId) AS Handled FROM leavemessagerecord A
                        LEFT JOIN leavemessagerecord B ON A.LeaveMessageRecordId=B.LeaveMessageRecordId AND B.IsRead=0
                        LEFT JOIN leavemessagerecord C ON A.LeaveMessageRecordId=C.LeaveMessageRecordId AND C.IsReply=1
                        WHERE DATE_FORMAT(A.CreateTime,'%y%m%d')=DATE_FORMAT(NOW(),'%y%m%d')";
            return this.databaseContext.Slave.QueryEntity<LeaveMessageCountEntity>(sql);
        }
    }
}
